﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Web.Security;
using System.Text;

public partial class datagrid : System.Web.UI.Page
{
    private static DataView dv = new DataView();
    private string sqlString;
    private string chartSqlString;
    DateTime bTime, eTime;
    string bYear = "", eYear = "", bMonth = "", eMonth = "", bDay = "", eDay = "", condition = "", granu = "", chartTitle = "";
    int chartSignal = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
//<<<<<<< .mine
        chartSignal = Convert.ToInt32(signalLabel.Text);
        promptLabel.Visible = false;
        string tempbYear, tempeYear;
        tempbYear = Request.QueryString["bYear"];
        tempeYear = Request.QueryString["eYear"];
        if (tempbYear != "请选择年" && tempeYear != "请选择年")
        {
            bYear = tempbYear;
            eYear = tempeYear;
        }
        granu = Request.QueryString["granularity"];
//=======
//        if(Session["group"] == null)
//            Session["group"] = 1;
//        bYear = Request.QueryString["bYear"];
//        eYear = Request.QueryString["eYear"];
//        string granu = Request.QueryString["granularity"];
//>>>>>>> .r97
        if (granu == "1")
        {
            if (tempbYear != "请选择年" && tempeYear != "请选择年")
            {
//<<<<<<< .mine
                bYear = Request.QueryString["bYear"];
                eYear = Request.QueryString["eYear"];
                bTime = Convert.ToDateTime(bYear + "-1-1");
                eTime = Convert.ToDateTime(eYear + "-12-31");
                SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
                conn.Open();
                if ((int)Session["group"] == 1)
                {
                    string tempSql = "select id from node where member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string nodeId = read.GetValue(0).ToString();
                    sqlString = "select * from node_year_lottery_view where Year between " + bYear + " and " + eYear + " and node = '" + nodeId + "'";
                }
                else if ((int)Session["group"] == 2)
                {
                    string tempSql = "select name from area,areamanager where area.id=areamanager.area and member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string areaName = read.GetValue(0).ToString();
                    sqlString = "select * from node_year_lottery_view where Year between " + bYear + " and " + eYear + " and area = '" + areaName + "'";
                }
                else if ((int)Session["group"] == 3)
                {
                    sqlString = "select * from area_year_lottery_view where Year between " + bYear + " and " + eYear + "";
                }
                if (condition != null && condition.Contains("区"))
                {
                    sqlString = "select * from node_year_lottery_view where year between" + bYear + " and " + eYear + " and area = '" + condition + "'";
                }
//=======
//                sqlString = "select * from node_year_lottery_view where year between'" + bYear + "' and '" + eYear + "' and area = '" + condition + "'";
//>>>>>>> .r97
            }
        }
        else if (granu == "2")
        {
            bYear = Request.QueryString["bYear"];
            eYear = Request.QueryString["eYear"];
            bMonth = Request.QueryString["bMonth"];
            eMonth = Request.QueryString["eMonth"];
            if (bYear != "请选择年" && eYear != "请选择年" && bMonth != "请选择月" && eMonth != "请选择月")
            {
                bTime = Convert.ToDateTime(bYear + "-" + bMonth + "-1");
                eTime = Convert.ToDateTime(eYear + "-" + eMonth + "-30");
                SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
                conn.Open();
                if ((int)Session["group"] == 1)
                {
                    string tempSql = "select id from node where member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string nodeId = read.GetValue(0).ToString();
                    sqlString = "select * from node_month_lottery_view where ((year>" + bYear +
                        " and year<" + eYear + ") or (year=" + bYear + " and year=" + eYear +
                        " and month>=" + bMonth + " and month<=" + eMonth + ") or (YEAR=" + bYear + " and MONTH>=" + bMonth +
                        ") or (YEAR=" + eYear + " and MONTH<=" + eMonth + ")) and node='" + nodeId + "'";
                }
                else if ((int)Session["group"] == 2)
                {
                    string tempSql = "select name from area,areamanager where area.id=areamanager.area and member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string areaName = read.GetValue(0).ToString();
                    sqlString = "select * from node_month_lottery_view where ((year>" + bYear +
                        " and year<" + eYear + ") or (year=" + bYear + " and year=" + eYear +
                        " and month>=" + bMonth + " and month<=" + eMonth + ") or (YEAR=" + bYear + " and MONTH>=" + bMonth +
                        ") or (YEAR=" + eYear + " and MONTH<=" + eMonth + ")) and area='" + areaName + "'";
                }
                else if ((int)Session["group"] == 3)
                {
                    sqlString = "select * from area_month_lottery_view where (year>" + bYear +
                        " and year<" + eYear + ") or (year=" + bYear + " and year=" + eYear +
                        " and month>=" + bMonth + " and month<=" + eMonth + ") or (YEAR=" + bYear + " and MONTH>=" + bMonth +
                        ") or (YEAR=" + eYear + " and MONTH<=" + eMonth + ")";
                }
                //sqlString = "select * from area_month_lottery_view where (year>'" + bYear +
                //    "' and year<'" + eYear + "') or (year='" + bYear + "' and year='" + eYear +
                //    "' and month>='" + bMonth + "' and month<='" + eMonth + "') or (YEAR='" + bYear + "' and MONTH>='" + bMonth +
                //    "') or (YEAR='" + eYear + "' and MONTH<='" + eMonth + "')";
            }
        }
        else if (granu == "3")
        {
            bYear = Request.QueryString["bYear"];
            eYear = Request.QueryString["eYear"];
            bMonth = Request.QueryString["bMonth"];
            eMonth = Request.QueryString["eMonth"];
            bDay = Request.QueryString["bDay"];
            eDay = Request.QueryString["eDay"];
            bTime = Convert.ToDateTime(bYear + " " + bMonth + " " + bDay);
            eTime = Convert.ToDateTime(eYear + " " + eMonth + " " + eDay);
            if (bYear != "请选择年" && eYear != "请选择年" && bMonth != "请选择月" && eMonth != "请选择月" && bDay != "请选择日" && eDay != "请选择日")
            {
                SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
                conn.Open();
                if ((int)Session["group"] == 1)
                {
                    string tempSql = "select id from node where member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string nodeId = read.GetValue(0).ToString();
                    sqlString = "select * from node_day_lottery_view where ((YEAR>" + bYear + " and YEAR<" + eYear +
                        ") or (YEAR=" + bYear + " and " + bYear + "=" + eYear + " and ((MONTH>" + bMonth + " and MONTH<" + eMonth +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "=" + eMonth + " and DAY>=" + bDay + " and DAY<=" + eDay +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "!=" + eMonth + " and day>=" + bDay + ") or (month=" + eMonth +
                        " and " + bMonth + "!=" + eMonth + " and DAY<=" + eDay + "))) or (YEAR=" + bYear + " and " + bYear + "!=" + eYear +
                        " and ((MONTH>" + bMonth + ") or (MONTH=" + bMonth + " and DAY>=" + bDay + "))) or (YEAR=" + eYear +
                        " and " + bYear + "!=" + eYear + " and ((MONTH<" + eMonth + ") or (MONTH=" + eMonth + " and DAY<=" + eDay + ")))) and node='" + nodeId + "'";
                }
                else if ((int)Session["group"] == 2)
                {
                    string tempSql = "select name from area,areamanager where area.id=areamanager.area and member=" + Session["uid"].ToString();
                    SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                    SqlDataReader read = tempCmd.ExecuteReader();
                    read.Read();
                    string areaName = read.GetValue(0).ToString();
                    sqlString = "select * from node_day_lottery_view where ((YEAR>" + bYear + " and YEAR<" + eYear +
                        ") or (YEAR=" + bYear + " and " + bYear + "=" + eYear + " and ((MONTH>" + bMonth + " and MONTH<" + eMonth +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "=" + eMonth + " and DAY>=" + bDay + " and DAY<=" + eDay +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "!=" + eMonth + " and day>=" + bDay + ") or (month=" + eMonth +
                        " and " + bMonth + "!=" + eMonth + " and DAY<=" + eDay + "))) or (YEAR=" + bYear + " and " + bYear + "!=" + eYear +
                        " and ((MONTH>" + bMonth + ") or (MONTH=" + bMonth + " and DAY>=" + bDay + "))) or (YEAR=" + eYear +
                        " and " + bYear + "!=" + eYear + " and ((MONTH<" + eMonth + ") or (MONTH=" + eMonth + " and DAY<=" + eDay + ")))) and area='" + areaName + "'";
                }
                else if ((int)Session["group"] == 3)
                {
                    sqlString = "select * from area_day_lottery_view where (YEAR>" + bYear + " and YEAR<" + eYear +
                        ") or (YEAR=" + bYear + " and " + bYear + "=" + eYear + " and ((MONTH>" + bMonth + " and MONTH<" + eMonth +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "=" + eMonth + " and DAY>=" + bDay + " and DAY<=" + eDay +
                        ") or (MONTH=" + bMonth + " and " + bMonth + "!=" + eMonth + " and day>=" + bDay + ") or (month=" + eMonth +
                        " and " + bMonth + "!=" + eMonth + " and DAY<=" + eDay + "))) or (YEAR=" + bYear + " and " + bYear + "!=" + eYear +
                        " and ((MONTH>" + bMonth + ") or (MONTH=" + bMonth + " and DAY>=" + bDay + "))) or (YEAR=" + eYear +
                        " and " + bYear + "!=" + eYear + " and ((MONTH<" + eMonth + ") or (MONTH=" + eMonth + " and DAY<=" + eDay + ")))";
                }
            }
        }
        if (!IsPostBack || condition.Contains("区"))
        {
            granu = "1";
            if (sqlString != null)
            {
                dv = GetData(sqlString).Tables[0].DefaultView;
            }
            else
            {
                dv = GetData().Tables[0].DefaultView;
            }
            if (dv != null)
            {
                GridView1 = dynamicGenerateColumns(GridView1, dv);
                GridView1.DataSource = dv;
                GridView1.AllowPaging = true;
                GridView1.PageSize = 5;
                GridView1.DataBind();
                GridView1.ShowHeader = true;
            }
            else
            {
                GridView1.Visible = false;
                Label1.Text = "无数据";
            }
            if ((int)Session["group"] == 1)
            {
                chartSignal = 11;
                granu = "1";
                bYear = eYear = (Convert.ToInt32(DateTime.Now.Year.ToString())-1).ToString();
                ChartGenerate(chartSignal);
            }
            else if ((int)Session["group"] == 2)
            {
                granu = "1";
                chartSignal = 12;
                SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
                conn.Open();
                string tempSql = "select name from area,areamanager where area.id=areamanager.area and member=" + Session["uid"].ToString();
                SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                SqlDataReader read = tempCmd.ExecuteReader();
                read.Read();
                string areaName = read.GetValue(0).ToString();
                chartSignal = 12;
                bYear = eYear = (Convert.ToInt32(DateTime.Now.Year.ToString()) - 1).ToString();
                ChartGenerate(12, areaName);
            }
            else if ((int)Session["group"] == 3)
            {
                chartSignal = 13;
                granu = "1";
                bYear = eYear = (Convert.ToInt32(DateTime.Now.Year.ToString()) - 1).ToString();
                ChartGenerate(13);
            }
            
        }
        //********************************************************************
        //**********************Generate Chart********************************

        if (granu != "1" && granu != "2" && granu != "3")
        {
            granu = "1";
            bYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
            eYear = bYear;
        }
        if (bYear == eYear && bMonth == eMonth && bDay == eDay)
        {
            if ((int)Session["group"] == 1)
            {
                chartSignal = 11;
                ChartGenerate(11);
            }
            else if ((int)Session["group"] == 2)
            {
                SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
                conn.Open();
                string tempSql = "select name from area,areamanager where area.id=areamanager.area and member=" + Session["uid"].ToString();
                SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                SqlDataReader read = tempCmd.ExecuteReader();
                read.Read();
                string areaName = read.GetValue(0).ToString();
                chartSignal = 12;
                ChartGenerate(12, areaName);
            }
            else if ((int)Session["group"] == 3)
            {
                chartSignal = 13;
                ChartGenerate(13);
            }
        }
        //if (isSavedLabel.Text == "1")
        //{
        //    sqlString = sqlStringLabel.Text;
        //    string tempArea = areaLabel.Text;
        //    string tempNode = nodeLabel.Text;
        //    int tempSignal = Convert.ToInt32(signalLabel.Text);
        //    ChartGenerate(tempSignal, tempArea, tempNode);
        //    isSavedLabel.Text = "0";
        //}
        //*********************************************************************
        CheckBox cb;
        int flag;
        flag = 0;
        for (int i = 1; i <= GridView1.Columns.Count; i++)
        {
            cb = new CheckBox();
            
            cb.Checked = true;
            cb.ID = "cb" + i.ToString();
            cb.Text = GridView1.Columns[i-1].HeaderText;
            cb.AutoPostBack = true;
            cb.CheckedChanged += new EventHandler(cb_CheckedChanged);
            Panel1.Controls.Add(cb);
   
        }
        if ((int)Session["group"] == 2)
        {
            Label1.Text = "请选择站点号（生成图表）：";
        }
        else if ((int)Session["group"] == 3)
        {
            Label1.Text = "请选择区（生成图表）：";
        }
        Button bt;
        try
        {
            foreach (Control control in this.Panel2.Controls)
            {
                Panel2.Controls.Remove(control);
            }
        }
        catch { }
        Panel Panel3 = new Panel();
        Panel2.Controls.Add(Panel3);
        for (int j = 1; j <= GridView1.Rows.Count; j++)
        {
            //GridView1.selec
            bt = new Button();
            bt.ID = "bt" + j.ToString();
            bt.CssClass = "areabt";
            bt.Text = GridView1.Rows[j - 1].Cells[0].Text;
            bt.Click += new EventHandler(areabt_Click);
            for (int k = 1; k <= j - 1; k++)
                if (bt.Text == GridView1.Rows[k - 1].Cells[0].Text)
                    flag++;
            if (flag == 0)
                Panel3.Controls.Add(bt);
            flag = 0;
        }
        Response.Clear();
        //****************************************************************************************
        //**********************show the save button**********************************************
        //if (sqlString == "" || sqlString == null)
        //{
        //    saveButton.Enabled = false;
        //}
        //else
        //{
        //    saveButton.Enabled = true;
        //}
        //****************************************************************************************
        if ((int)Session["group"] == 1)
        {
            saveButton.Visible = false;
        }
    }

    //********************************************************************************************
    //*****************funciont name: ChartGenerate(int signal, string area = "")*****************
    //*****************function to generate the chart of the form*********************************
    //*****************signal = (11:same time and group is NO.1, 12:same time and group is NO.2***
    //*************************13:same time and group is NO.3, 21:same node and group is NO.1*****
    //*************************22:same node and group is NO.2, 23:same area and group is NO.3)****
    //*****************area can be used as Area Name or Node ID***********************************
    //*****************node can only be used when signal=22***************************************
    private void ChartGenerate(int signal, string area = "", string node = "")
    {
        signalLabel.Text = signal.ToString();
        string tempSqlstring = "";
        if (signal == 11)
        { 
            //no need to show the chart
        }
        else if (signal == 12)
        {
            if (granu == "1")
            {
                if (area != "")
                {
                    tempSqlstring = "select node,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_year_lottery_view where area = '" 
                        + area + "' and year = " + bYear;
                    Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + area + "各站点彩票销售情况";
                    chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                    areaLabel.Text = area;
                    Chartlet1.XLabels.UnitText = "站点号";
                }
            }
            else if (granu == "2")
            {
                if (area != "")
                {
                    tempSqlstring = "select node,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_month_lottery_view where area = '" 
                        + area + "' and year=" + bYear + " and month=" + bMonth;
                    Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + area + "区各站点彩票销售情况";
                    chartTitle = "图表:" + bYear + "年" + bMonth + "月" + area + "各站点彩票销售情况";
                    chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                    areaLabel.Text = area;
                    Chartlet1.XLabels.UnitText = "站点号";
                }
            }
            else if (granu == "3")
            {
                if (area != "")
                {
                    tempSqlstring = "select node,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_day_lottery_view where area = '"
                        + area + "' and year=" + bYear + " and month=" + bMonth + " and day=" + bDay;
                    Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日" + area + "区各站点彩票销售情况";
                    chartTitle = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日" + area + "各站点彩票销售情况";
                    chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                    areaLabel.Text = area;
                    Chartlet1.XLabels.UnitText = "站点号";
                }
            }
        }
        else if (signal == 13)
        {
            if (granu == "1")
            {
                tempSqlstring = "select area,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from area_year_lottery_view where year=" + bYear;
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年上海市各区彩票销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "区名";
            }
            else if (granu == "2")
            {
                tempSqlstring = "select area,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from area_month_lottery_view where year="
                    + bYear + " and month=" + bMonth;
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月上海市各区彩票销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "区名";
            }
            else if (granu == "3")
            {
                tempSqlstring = "select area,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from area_day_lottery_view where year="
                    + bYear + " and month=" + bMonth + " and day=" + bDay;
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日上海市各区彩票销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "区名";
            }
        }
        else if (signal == 21)
        {
            if (granu == "1")
            {
                tempSqlstring = "select year,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_year_lottery_view where node='"
                    + area + "' and year between '" + bYear + "' and '" + eYear + "'";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年至" + eYear + "年" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "2")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] "
                    + "from node_month_lottery_view where node='" + area + "' and ((year>'" + bYear +
                        "' and year<'" + eYear + "') or (year='" + bYear + "' and year='" + eYear +
                        "' and month>='" + bMonth + "' and month<='" + eMonth + "') or (YEAR='" + bYear + "' and MONTH>='" + bMonth +
                        "') or (YEAR='" + eYear + "' and MONTH<='" + eMonth + "'))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月至" + eYear + "年" + eMonth
                    + "月" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "3")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month)+'-'+CONVERT(varchar,day),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_day_lottery_view where node='"
                    + area + "' and ((YEAR>'" + bYear + "' and YEAR<'" + eYear +
                    "') or (YEAR='" + bYear + "' and '" + bYear + "'='" + eYear + "' and ((MONTH>'" + bMonth + "' and MONTH<'" + eMonth +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'='" + eMonth + "' and DAY>='" + bDay + "' and DAY<='" + eDay +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'!='" + eMonth + "' and day>='" + bDay + "') or (month='" + eMonth +
                    "' and '" + bMonth + "'!='" + eMonth + "' and DAY<='" + eDay + "'))) or (YEAR='" + bYear + "' and '" + bYear + "'!='" + eYear +
                    "' and ((MONTH>'" + bMonth + "') or (MONTH='" + bMonth + "' and DAY>='" + bDay + "'))) or (YEAR='" + eYear +
                    "' and '" + bYear + "'!='" + eYear + "' and ((MONTH<'" + eMonth + "') or (MONTH='" + eMonth + "' and DAY<='" + eDay + "'))))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日至" + eYear + "年" + eMonth + "月" + eDay
                    + "日" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                Chartlet1.XLabels.UnitText = "时间";
            }
        }
        else if (signal == 22)
        {
            if (granu == "1")
            {
                tempSqlstring = "select year,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_year_lottery_view where area='"
                    + area + "' and node='" + node + "' and year between '" + bYear + "' and '" + eYear + "'";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年至" + eYear + "年" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                nodeLabel.Text = node;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "2")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] "
                    + "from node_month_lottery_view where area='" + area + "' and node='" + node + "' and ((year>'" + bYear +
                        "' and year<'" + eYear + "') or (year='" + bYear + "' and year='" + eYear +
                        "' and month>='" + bMonth + "' and month<='" + eMonth + "') or (YEAR='" + bYear + "' and MONTH>='" + bMonth +
                        "') or (YEAR='" + eYear + "' and MONTH<='" + eMonth + "'))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月至" + eYear + "年" + eMonth
                    + "月" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                nodeLabel.Text = node;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "3")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month)+'-'+CONVERT(varchar,day),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from node_day_lottery_view where area='"
                    + area + "' and node='" + node + "' and ((YEAR>'" + bYear + "' and YEAR<'" + eYear +
                    "') or (YEAR='" + bYear + "' and '" + bYear + "'='" + eYear + "' and ((MONTH>'" + bMonth + "' and MONTH<'" + eMonth +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'='" + eMonth + "' and DAY>='" + bDay + "' and DAY<='" + eDay +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'!='" + eMonth + "' and day>='" + bDay + "') or (month='" + eMonth +
                    "' and '" + bMonth + "'!='" + eMonth + "' and DAY<='" + eDay + "'))) or (YEAR='" + bYear + "' and '" + bYear + "'!='" + eYear +
                    "' and ((MONTH>'" + bMonth + "') or (MONTH='" + bMonth + "' and DAY>='" + bDay + "'))) or (YEAR='" + eYear +
                    "' and '" + bYear + "'!='" + eYear + "' and ((MONTH<'" + eMonth + "') or (MONTH='" + eMonth + "' and DAY<='" + eDay + "'))))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日至" + eYear + "年" + eMonth + "月" + eDay
                    + "日" + area + "" + node + "站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                nodeLabel.Text = node;
                Chartlet1.XLabels.UnitText = "时间";
            }
        }
        else if (signal == 23)
        {
            if (granu == "1")
            {
                tempSqlstring = "select year,[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] from area_year_lottery_view where area='" 
                    + area + "' and year between '" + bYear + "' and '" + eYear + "'";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年至" + eYear + "年" + area + "各站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "2")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] "
                    + "from area_month_lottery_view where area='" + area + "' and ((year>'" + bYear +
                        "' and year<'" + eYear + "') or (year='" + bYear + "' and year='" + eYear +
                        "' and month>='" + bMonth + "' and month<='" + eMonth + "') or (YEAR='" + bYear + "' and MONTH>='" + bMonth +
                        "') or (YEAR='" + eYear + "' and MONTH<='" + eMonth + "'))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月至" + eYear + "年" + eMonth
                    + "月" + area + "各站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                Chartlet1.XLabels.UnitText = "时间";
            }
            else if (granu == "3")
            {
                tempSqlstring = "select CONVERT(varchar,year)+'-'+CONVERT(varchar,month)+'-'+CONVERT(varchar,day),[双色球],[福彩3D],[七乐彩],[东方6+1],[15选5],[天天彩选4],[时时乐] "
                    + "from area_day_lottery_view where area='" + area + "' and ((YEAR>'" + bYear + "' and YEAR<'" + eYear +
                    "') or (YEAR='" + bYear + "' and '" + bYear + "'='" + eYear + "' and ((MONTH>'" + bMonth + "' and MONTH<'" + eMonth +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'='" + eMonth + "' and DAY>='" + bDay + "' and DAY<='" + eDay +
                    "') or (MONTH='" + bMonth + "' and '" + bMonth + "'!='" + eMonth + "' and day>='" + bDay + "') or (month='" + eMonth +
                    "' and '" + bMonth + "'!='" + eMonth + "' and DAY<='" + eDay + "'))) or (YEAR='" + bYear + "' and '" + bYear + "'!='" + eYear +
                    "' and ((MONTH>'" + bMonth + "') or (MONTH='" + bMonth + "' and DAY>='" + bDay + "'))) or (YEAR='" + eYear +
                    "' and '" + bYear + "'!='" + eYear + "' and ((MONTH<'" + eMonth + "') or (MONTH='" + eMonth + "' and DAY<='" + eDay + "'))))";
                Chartlet1.ChartTitle.Text = "图表:" + bYear + "年" + bMonth + "月" + bDay + "日至" + eYear + "年" + eMonth + "月" + eDay
                    + "日" + area + "各站点销售情况";
                chartTitleLabel.Text = Chartlet1.ChartTitle.Text;
                areaLabel.Text = area;
                Chartlet1.XLabels.UnitText = "时间";
            }
        }
        if (tempSqlstring != "")
        {
            signalLabel.Text = signal.ToString();
            if (GetData(tempSqlstring).Tables[0].Rows.Count > 0)
            {
                Chartlet1.BindChartData(GetData(tempSqlstring));
                Chartlet1.YLabels.UnitText = "张数";
            }
        }
        signalLabel.Text = signal.ToString();
        areaLabel.Text = area;
        nodeLabel.Text = node;
    }
    //*******************************************************************************************************************************

    private DataTable ColRowReverse(DataTable dt)
    {
        DataTable dtNew = new DataTable();
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            dtNew.Columns.Add("Col" + (i + 1).ToString(), typeof(string));
        }
        foreach (DataColumn dc in dt.Columns)
        {
            DataRow drNew = dtNew.NewRow();
            drNew["ColumnName"] = dc.ColumnName;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                drNew[i + 1] = dt.Rows[i][dc].ToString();
            }
            dtNew.Rows.Add(drNew);
        }
        return dtNew;
    }

    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        string sortExpression = e.SortExpression;
        for (int i = 0; i < GridView1.Columns.Count; i++)
        {
            GridView1.Columns[i].Visible = true; 
        }
        if (GridViewSortDirection == SortDirection.Ascending)
        {
            GridViewSortDirection = SortDirection.Descending;
            SortGridView(sortExpression, "DESC");
        }
        else
        {
            GridViewSortDirection = SortDirection.Ascending;
            SortGridView(sortExpression, "ASC");
        }
        string name;
        for (int i = 1; i <= GridView1.Columns.Count; i++)
        {
            name = "cb" + i.ToString();
            CheckBox tpcb = (CheckBox)Panel1.FindControl(name);
            if (!tpcb.Checked)
            {
                GridView1.Columns[i - 1].Visible = false;
            }
        }
    }

    public SortDirection GridViewSortDirection
    {
        get
        {
            if (ViewState["sortDirection"] == null)
                ViewState["sortDirection"] = SortDirection.Descending;
            return (SortDirection)ViewState["sortDirection"];
        }
        set { ViewState["sortDirection"] = value; }
    }
    private void SortGridView(string sortExpression, string direction)
    {
        DataTable dt = GetData(sqlString).Tables[0];
        dv = dt.DefaultView;
        dv.Sort = sortExpression + " "+ direction;
        GridView1.DataSource = dv;
        GridView1.DataBind();
    }

    
    private DataSet GetData(string sql = "")
    {
        SqlConnection conn = new SqlConnection(
        ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
        conn.Open();
        if (sql == "" || sql == null)
        {
            if ((int)Session["group"] == 3)
            {
                sql = "select * from area_year_lottery_view where year = " + ((int)DateTime.Now.Year - 1).ToString();
            }
            else if ((int)Session["group"] == 2)
            {
                string tempSql = "select name from Area,AreaManager where Area.id=AreaManager.area and AreaManager.member=" + Session["uid"].ToString();
                SqlCommand tempCmd = new SqlCommand(tempSql,conn);
                SqlDataReader read = tempCmd.ExecuteReader();
                read.Read();
                string areaName = read.GetValue(0).ToString();
                sql = "select * from node_year_lottery_view where year = " + ((int)DateTime.Now.Year - 1).ToString() + " and area = '" + areaName + "'";
            }
            else if ((int)Session["group"] == 1)
            {
                string tempSql = "select id from node where member = " + Session["uid"].ToString();
                SqlCommand tempCmd = new SqlCommand(tempSql, conn);
                SqlDataReader read = tempCmd.ExecuteReader();
                read.Read();
                string nodeId = read.GetValue(0).ToString();
                sql = "select * from node_year_lottery_view where year = " + ((int)DateTime.Now.Year - 1).ToString() + " and node = '" + nodeId + "'";
            }
        }
        conn.Close();
        conn.Open();
        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);
        SqlCommand cmd = new SqlCommand(sql, conn);
        da.Fill(ds);
        return ds;
    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataSource = dv;
        GridView1.DataBind();
        Panel2.Controls.Clear();
        for (int i = 1; i < Panel2.Controls.Count; i++)
        {
            //this.RemovedControl(Panel2.FindControl("bt" + i));
            //Panel2.FindControl("bt" + i).Visible = false;
        }
        Button bt;
        for (int j = 1; j <= GridView1.Rows.Count; j++)
        {
            //GridView1.selec
            bt = new Button();
            bt.ID = "bt" + j.ToString();
            bt.CssClass = "areabt";
            bt.Text = GridView1.Rows[j - 1].Cells[0].Text;
            bt.Click += new EventHandler(areabt_Click);
            Panel2.Controls.Add(bt);
        }
    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        for(int i = 0; i < 5; i++)
            GridView1.Rows[i].Cells[0].Visible = false;
        GridView1.HeaderRow.Cells[0].Visible = false;
    }
    protected void areabt_Click(object sender, EventArgs e)
    {
        Button currentButton = (Button)sender;
        //condition = currentButton.Text;
        //for (int i = 1; i < Panel1.Controls.Count; i++)
        //{
        //    this.RemovedControl(Panel1.FindControl("cb" + i));
        //    Panel1.FindControl("cb" + i).Visible = false;
        //}
        //for (int i = 1; i < Panel2.Controls.Count; i++)
        //{
        //    this.RemovedControl(Panel2.FindControl("bt" + i));
        //    Panel2.FindControl("bt" + i).Visible = false;
        //}
        //this.Page_Load(this,null);
        if ((int)Session["group"] == 1)
        {
            if (granu == null)
            {
                granu = "1";
                bYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
                eYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
            }
            chartSignal = 21;
            ChartGenerate(21, currentButton.Text);
        }
        else if ((int)Session["group"] == 2)
        {
            SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
            conn.Open();
            string tempSql = "select name from Area,AreaManager where Area.id=AreaManager.area and AreaManager.member=" + Session["uid"].ToString();
            SqlCommand tempCmd = new SqlCommand(tempSql,conn);
            SqlDataReader read = tempCmd.ExecuteReader();
            read.Read();
            string areaName = read.GetValue(0).ToString();
            if (granu == null)
            {
                granu = "1";
                bYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
                eYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
            }
            chartSignal = 22;
            ChartGenerate(22, areaName, currentButton.Text);
        }
        else if ((int)Session["group"] == 3)
        {
            if (granu == null)
            {
                granu = "1";
                bYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
                eYear = (Convert.ToInt32(DateTime.Now.Year) - 1).ToString();
            }
            chartSignal = 23;
            ChartGenerate(23, currentButton.Text);
        }
    }
    public static GridView dynamicGenerateColumns(GridView gv, DataView dv)
    {
        // 把GridView的自动产生列设置为false,否则会出现重复列
        gv.AutoGenerateColumns = false;

        // 清空所有的Columns
        gv.Columns.Clear();
        //gv.Columns[0].ControlStyle.Width = 100;
        // 遍历DataTable 的每个Columns,然后添加到GridView中去 
        //gv.Width = 400;
        foreach (DataColumn item in dv.Table.Columns)
        {
            BoundField col = new BoundField();
            col.HeaderText = item.ColumnName;
            col.DataField = item.ColumnName;
            col.SortExpression = item.ColumnName;
            col.Visible = true;
            gv.Columns.Add(col);
        }
        return gv;
    }
    public void cb_CheckedChanged(object sender, EventArgs e)
    {
        CheckBox cb = (CheckBox)sender;
        int index = Convert.ToInt32(cb.ID.Substring(2)) - 1;
        if (!cb.Checked)
        {
            GridView1.Columns[index].Visible = false;
        }
        else
        {
            GridView1.Columns[index].Visible = true;
            dv = GetData().Tables[0].DefaultView;
        }
    }



    protected void saveButton_Click(object sender, EventArgs e)
    {
        int rowNum;
        string tempBtime, tempEtime, tempGranu;
        tempGranu = granu;
        if (tempGranu == "1")
        {
            if (bYear == "" || eYear == "")
            {
                tempBtime = DateTime.Now.Year.ToString() + "-1-1";
                tempEtime = DateTime.Now.Year.ToString() + "-12-31";
            }
            else
            {
                tempBtime = bYear + "-1-1";
                tempEtime = eYear + "-12-31";
            }
        }
        else if (tempGranu == "2")
        {
            if (bYear == "" || eYear == "" || bMonth == "" || eMonth == "")
            {
                tempBtime = DateTime.Now.Year.ToString() + "-1-1";
                tempEtime = DateTime.Now.Year.ToString() + "-12-31";
                tempGranu = "1";
            }
            else
            {
                tempBtime = bYear + "-" + bMonth + "-1";
                tempEtime = eYear + "-" + eMonth + "-1";
            }
        }
        else if (tempGranu == "3")
        {
            if (bYear == "" || eYear == "" || bMonth == "" || eMonth == "" || bDay == "" || eDay == "")
            {
                tempBtime = DateTime.Now.Year.ToString() + "-1-1";
                tempEtime = DateTime.Now.Year.ToString() + "-12-31";
                tempGranu = "1";
            }
            else
            {
                tempBtime = bYear + "-" + bMonth + "-" + bDay;
                tempEtime = eYear + "-" + eMonth + "-" + eDay;
            }
        }
        else
        {
            tempBtime = DateTime.Now.Year.ToString() + "-1-1";
            tempEtime = DateTime.Now.Year.ToString() + "-12-31";
            tempGranu = "1";
        }
        SqlConnection conn = new SqlConnection(
                    ConfigurationManager.ConnectionStrings["testLottery"].ConnectionString);
        conn.Open();
        string tempSql = "insert into Report (name,btime,etime,granularity,member,sqlstring,chartsignal,areaname,nodeid) values ('"
            + chartTitleLabel.Text + "','" + tempBtime + "','" + tempEtime + "'," + tempGranu + "," 
            + Session["uid"].ToString() + ",'" + GetSqlString(sqlString) + "'," + signalLabel.Text + ",'" + areaLabel.Text
            + "','" + nodeLabel.Text + "')";
        SqlCommand tempCmd = new SqlCommand(tempSql, conn);
        rowNum = tempCmd.ExecuteNonQuery();
        if (rowNum == 1)
        {
            //this.Response.Write("<script language='javascript'>alert('保存成功！')</script>");
            promptLabel.Visible = true;
            promptLabel.Text = "保存成功！";
            sqlStringLabel.Text = sqlString;
            isSavedLabel.Text = "1";
        }
        else
        {
            //this.Response.Write("<script language='javascript'>alert('日保存失败！')</script>");
            promptLabel.Visible = true;
            promptLabel.Text = "日保存失败！";
        }
    }


    //*********************************************************************************************
    //******************function is used to replace all "'" to "''" in a sql string****************
    private string GetSqlString(string str)
    {
        StringBuilder eStr = new StringBuilder(str);
        eStr = eStr.Replace("'","''");
        return eStr.ToString();
    }
    //*********************************************************************************************
}
